<?php
class Advertisesactivity_model extends CRM_Model{
	
	public function __construct(){
		parent::__construct();
	}
	
	public function GetSourcesDS(){
		// Получаем рекламные площадки, которые должны работать в заданный период
		$this->db->select('_advertisessources.rid as source_rid,  
							_advertisessources.source_name as source_name, 
							_advertisestypes.type_name as source_type,
							_advertises_rows.advertises_name as advertises_name,
							_advertises_rows.bdate as bdate,
							_advertises_rows.edate as edate');
		$this->db->from('_advertisessources');
		$this->db->join('_advertisestypes', '_advertisestypes.rid = _advertisessources._advertisestypes_rid');
		$this->db->join('_advertises_rows', '_advertises_rows._advertisessources_rid = _advertisessources.rid', 'LEFT');
		$this->db->join('_advertises_headers', '_advertises_rows._advertises_headers_rid = _advertises_headers.rid', 'LEFT');
		$this->db->join('_outgoing_incoming', "_outgoing_incoming._documents_rid = _advertises_headers._documents_rid AND _outgoing_incoming.oper_type='P'", 'LEFT');
		$this->db->join('_filials', '_outgoing_incoming._filials_rid = _filials.rid', 'LEFT');
		$this->db->where(array('_advertises_rows.edate >='=>$this->toMySQLDate($_POST['bdate']), '_advertises_rows.bdate <='=>$this->toMySQLDate($_POST['edate'])));
		$filial = trim($_POST['filial']);
		$source = trim($_POST['source']);
		if(!empty($filial)) $this->db->where(array('_filials_rid'=>$filial));
		if(!empty($source)) $this->db->where(array('_advertisessources_rid'=>$source));
		//$this->db->groupby('_advertisessources.rid');
		$query = $this->db->get();
		if($query->num_rows()>0) return $query->result();
		return array();
	}
	
	public function GetSourceDS($source){
		// Для каждой рекламной площадки просматриваем как мы за нее платили
		$this->db->select('_advertises_rows.bdate as bdate, _advertises_rows.edate as edate, _advertises_rows.advertises_name as advertises_name');
		$this->db->from('_advertises_rows');
		$this->db->join('_advertisessources', '_advertises_rows._advertisessources_rid = _advertisessources.rid');
		$this->db->join('_calls_rows', '_calls_rows._advertisessources_rid= _advertisessources.rid', 'LEFT'); 
		$this->db->join('_calls_headers', '_calls_headers.rid= _calls_rows._calls_headers_rid', 'LEFT');
		$this->db->where(array('_advertisessources.rid'=>$source->source_rid, '_advertises_rows.edate >='=>$this->toMySQLDate($_POST['bdate']), '_advertises_rows.bdate <='=>$this->toMySQLDate($_POST['edate'])));
		$query = $this->db->get();
		return $query->result(); 
	}
	
	public function GetContacts($source_rid){
		$this->db->select('_filials.rid as filialRid, _filials.name  as filialName, _advertisessources.rid as sourceRid, _calls_headers.date_doc as contactDate, count(_calls_headers.rid) dateQuan');
		$this->db->from('_calls_rows');
		$this->db->join('_calls_headers', '_calls_headers.rid= _calls_rows._calls_headers_rid'); 
		$this->db->join('_advertisessources', '_calls_rows._advertisessources_rid = _advertisessources.rid');
		$this->db->join('_users', '_calls_rows.modifier_users_rid = _users.rid');
		$this->db->join('_employeers', '_users._employeers_rid = _employeers.rid');
		$this->db->join('_filials', '_employeers._filials_rid = _filials.rid');
		$this->db->where(array('_calls_headers.date_doc >='=> $_POST['bdate'], '_calls_headers.date_doc <='=>$_POST['edate'], '_advertisessources.rid'=>$source_rid));
		$this->db->groupby('_calls_headers.date_doc,_filials.rid, _advertisessources.rid');
		$this->db->orderby('_filials.name');
		$query = $this->db->get();
		if($query->num_rows()) return $query->result();
		return False;
	}
	
	public function to_Excel(){
		$xls =& new Spreadsheet_Excel_Writer(); 
		$xls->send("advertise.xls"); 
		$sheet =& $xls->addWorksheet('advertise');
		$sheet->setColumn(1, 100, 2);
		$titleFormat =& $xls->addFormat();
		$titleFormat->setFontFamily('Helvetica');
		$titleFormat->setBold();
		$titleFormat->setSize('11');
		
		$sourcenameFormat =& $xls->addFormat();
		$sourcenameFormat->setFontFamily('Helvetica');
		$sourcenameFormat->setSize('10');
		$sourcenameFormat->setBold();

		$textFormat =& $xls->addFormat();
		$textFormat->setFontFamily('Helvetica');
		$textFormat->setSize('10');
			//$textFormat->setBold();
		$colorFormat =& $xls->addFormat();
		$colorFormat->setBgColor('gray');
		
		$advNameFormat =& $xls->addFormat();
		$advNameFormat->setTextRotation(90);
		
		$titleText = iconv('UTF-8', 'Windows-1251', "Отчет по активности рекламных носителей за {$_POST['bdate']} - {$_POST['edate']}");
		$sheet->write(0,0,$titleText,$titleFormat);
		
		$currRow = 2;
		$ds = $this->GetSourcesDS();
		$sourcesArr = array(); 
		foreach($ds as $source)	{
			if(!isset($sourcesArr[$source->source_rid])) $sourcesArr[$source->source_rid] = $source->source_type.' - '.$source->source_name;
		}
		foreach($sourcesArr as $key=>$name){
			//$sheet =& $xls->addWorksheet('advertise');
			$sheet->write($currRow, 0,iconv('UTF-8', 'Windows-1251', $name),$titleFormat);
			$headerRowNum = $currRow+1; $currRow += 2;
			$sheet->setRow($headerRowNum, 50);
			$contactsArr = $this->GetContacts($key);
			//var_dump($contactsArr);
			$bdateArr = explode('.', $_POST['bdate']);
			$edateArr = explode('.', $_POST['edate']);
			$bdateDATE = mktime(0,0,0,$bdateArr[1],$bdateArr[0],$bdateArr[2]);
			$edateDATE = mktime(0,0,0,$edateArr[1],$edateArr[0],$edateArr[2]);
			$daysCounter = $bdateArr[0];
			$totalsArr = array();
			while($edateDATE>=$bdateDATE){
				$currColl = 1;
				$sheet->write($currRow, 0,iconv('UTF-8', 'Windows-1251', date('d.m.Y', $bdateDATE)),$textFormat);				
				foreach($ds as $source){
					if($source->source_rid != $key) continue;
					$dB = explode('-', $source->bdate);
					$dE = explode('-', $source->edate);
					$dB = mktime(0,0,0,$dB[1],$dB[2],$dB[0]);
					$dE = mktime(0,0,0,$dE[1],$dE[2],$dE[0]);
					if($dB <= $bdateDATE && $bdateDATE <= $dE) {
						$sheet->write($headerRowNum, $currColl, $source->advertises_name,$advNameFormat);
						$sheet->write($currRow, $currColl, '',$colorFormat);
						$currColl++;
					} 
					else {
						$sheet->write($currRow, $currColl, '',$titleFormat);
						$currColl++;
					}
				}
				// Формируем шапку филиалов
				$columnAllNum = $currColl; $currColl++;
				$sheet->write($headerRowNum, $columnAllNum, iconv('UTF-8', 'Windows-1251', 'Всего'), $advNameFormat);
				$allContactsByDate = 0;
				if($contactsArr){
					foreach($contactsArr as $contact){
						$contactDate = explode('-', $contact->contactDate);
						$contactDate = mktime(0,0,0,$contactDate[1],$contactDate[2],$contactDate[0]);
						if($contactDate==$bdateDATE) {
							$sheet->write($headerRowNum, $currColl, $contact->filialName, $advNameFormat);
							$sheet->write($currRow, $currColl, $contact->dateQuan, $textFormat);
							$allContactsByDate += $contact->dateQuan;
							if(!isset($totalsArr[$currColl])) $totalsArr[$currColl] = 0; 
							$totalsArr[$currColl] += $contact->dateQuan;
							$currColl++;
						} 
						else {
							$sheet->write($currRow, $currColl, '0', $textFormat);
							if(!isset($totalsArr[$currColl])) $totalsArr[$currColl] = 0;
							$currColl++;
						}
					}
				}
				$sheet->write($currRow, $columnAllNum, $allContactsByDate, $textFormat);
				$daysCounter++; 
				$bdateDATE = mktime(0,0,0,$bdateArr[1],$daysCounter,$bdateArr[2]);
				$currRow++;
			}
			$sheet->write($currRow, 0, iconv('UTF-8', 'Windows-1251', 'Итого'), $titleFormat);
			$total = 0;
			foreach($totalsArr as $totalColumn=>$totalValue) {
				$sheet->write($currRow, $totalColumn, $totalValue, $titleFormat);
				$total += $totalValue;
			}
			$sheet->write($currRow, $columnAllNum, $total, $titleFormat);
			$currRow += 2;
		}
		$xls->close(); 
	}
}
?>